package com.niit.dao;

import com.niit.entity.Users;
import com.niit.utils.JDBCUtils;
import com.niit.utils.UserDataGenerator;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UsersDao {
    //    创建一个登录方法
    //    方法用来做什么？-----验证用户输入的用户名和密码是否正确
//    方法给谁来访问？----所有人--public
//    方法的返回值类型是什么？---Boolean
//    这个方法需要输入什么参数？--phoneNum  password
    public Boolean login(String phoneNum, String password) throws SQLException {
        ResultSet rs = JDBCUtils.executeQuery(
                "SELECT * FROM users where phone_num = ? and `password` = ?", phoneNum, password);
        return rs.next();
    }


    public Boolean addUser(Users users) {
        int i = JDBCUtils.executeUpdate("INSERT into users " +
                        "(phone_num,password,create_time,role) VALUES (?,?,now(),?)"
                , users.getPhoneNum(), users.getPassword(), users.getRole());
        return i > 0 ? true : false;
    }

    /**
     * 根据用户id删除用户
     * @param userId 用户id
     * @return
     */
    public Boolean deleteUser(Integer userId) {
        int i = JDBCUtils.executeUpdate("DELETE FROM users where user_id = ?" , userId);
        return i > 0 ? true : false;
    }

    /**
     * 更新用户信息
     * @param users
     * @return
     */
    public Boolean updateUser(Users users) {
        int i = JDBCUtils.executeUpdate(
                "UPDATE users set `password` = ? ,gender = ?,birthday = ?, role = ? where user_id = ?"
                , users.getPassword(),users.getGender(),users.getBirthday(),users.getRole(),users.getUserId());
        return i > 0 ? true : false;
    }

//    对用户或其他信息的查询一般会提供两种方式：1.根据id查询用户   2.查询用户列表（带分页,带多字段的模糊搜索)

    /**
     * 根据用户id查询用户
     * @param userId
     * @return
     * @throws SQLException
     */
    public Users queryUserById(Integer userId) throws SQLException {
        ResultSet rs = JDBCUtils.executeQuery("SELECT * from users where user_id = ?", userId);
        Users users = new Users();
        if(rs.next()){
            users.setUserId(rs.getInt("user_id"));
            users.setPhoneNum(rs.getString("phone_num"));
            users.setPassword(rs.getString("password"));
            users.setGender(rs.getInt("gender"));
            users.setBirthday(rs.getDate("birthday"));
            users.setCreateTime(rs.getDate("create_time"));
            users.setRole(rs.getInt("role"));
        }
        return users;
    }

    /**
     * 查询用户列表
     * @param phoneNum 用户名，可以模糊搜索
     * @param gender 性别
     * @param role 角色
     * @param page 页数
     * @param pageSize 每页多少条数据
     * @return
     */
    public List<Users> listUsers(String phoneNum,Integer gender,Integer role,Integer page,Integer pageSize) throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT * FROM users where 1=1 ");
        if(phoneNum !=null && !phoneNum.equals("")){
            sql.append(" and phone_num like '%").append(phoneNum).append("%'");
        }
        if(gender != null){
            sql.append("and gender = '").append(gender).append("'");
        }
        if(role != null){
            sql.append("and role = ").append(role);
        }
        if(page != null && pageSize != null){
            sql.append(" limit ").append((page-1)*pageSize).append(",").append(pageSize);
        }
        System.out.println(sql.toString());
        ResultSet rs = JDBCUtils.getConnection().createStatement().executeQuery(sql.toString());
        List<Users> userList = new ArrayList<>();
        while(rs.next()){
            Users users = new Users();
            users.setUserId(rs.getInt("user_id"));
            users.setPhoneNum(rs.getString("phone_num"));
            users.setPassword(rs.getString("password"));
            users.setGender(rs.getInt("gender"));
            users.setBirthday(rs.getDate("birthday"));
            users.setCreateTime(rs.getDate("create_time"));
            users.setRole(rs.getInt("role"));
            userList.add(users);
        }
        JDBCUtils.close(null,null,rs);
        return userList;

    }

    public static void main(String[] args) {
        try {
            List<Users> users = new UsersDao().listUsers(null, 1, 1, 2, 10);
            for (Users user : users) {
                System.out.println(user);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }






}
